package com.mysql;

import org.json.JSONArray;
import org.json.JSONObject;
import utils.JDBCUtils;

import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;


/**
 * 以下为存放增删改查等的方法的类
 */
public class MysqlMethod {
    //添加学生信息方法
    public static void addevent(Integer age,Integer classId,String className,String createTime,String name,String phone,String remark,String sex,Integer studentId,String studentNo ) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateStr =  format.format(date);
        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into student01 (age,className,createTime,name,phone,remark,sex,studentNo) values(?,?,?,?,?,?,?,?)";
            pre = conn.prepareStatement(sql);
            //System.out.println(age);
            pre.setInt(1, age);
            //pre.setInt(2, classId);
            pre.setString(2, className);
            pre.setString(3, dateStr);
            pre.setString(4, name);
            pre.setString(5, phone);
            pre.setString(6, remark);
            pre.setString(7, sex);
            //pre.setInt(9, studentId);
            pre.setString(8, studentNo);
            pre.executeUpdate();
            JDBCUtils.close(conn,pre,re);//关闭数据库
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    //查询全部学生数据
    public static String selectAll(String pageNum,String pageSize,String studentNo,String name) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        JSONArray arr = new JSONArray();
        int pageIndex = (Integer.valueOf(pageNum) - 1) * 10;
        try {
            conn = JDBCUtils.getConnection();
            //拼接where条件字段
            String whereStr = "where 1=1";
            if (studentNo != null && !"".equals(studentNo)) {
                whereStr = whereStr + " and studentNo like '%" + studentNo + "%'";
            }
            //  "where class_no like '%123%' and name like '%hello%';
            if (name != null && !"".equals(name)) {
//                whereStr = whereStr.equals("where")? whereStr + " name like '%"+name+"%'":whereStr + " and name like '%"+name+"%'";
                whereStr = whereStr + " and name like '%" + name + "%'";
            }
            String sql = "select * from student01 " + whereStr + " limit " + pageIndex + "," + pageSize;
            pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery(sql);
            //返回查询的结果
            ResultSet set = pre.executeQuery();
            arr =resultSetToJson(set);
            JDBCUtils.close(conn,pre,re);//关闭数据库
            //System.out.println(arr.toString());


        } catch (SQLException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return arr.toString();
    }
    //将ResultSet转换成json的函数
    public static JSONArray resultSetToJson(ResultSet rs) throws SQLException, UnsupportedEncodingException {
        // json数组
        JSONArray array = new JSONArray();
        // 获取列数
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        // 遍历ResultSet中的每条数据
        while (rs.next()) {
            JSONObject jsonObj = new JSONObject();
            // 遍历每一列
            for (int i = 1; i <= columnCount; i++) {
                String value = null;
                String columnName = metaData.getColumnLabel(i);//列名称
                if (rs.getString(columnName) != null && !rs.getString(columnName).equals("")) {
                    value = new String(rs.getBytes(columnName), "UTF-8");//列的值,有数据则转码
                    //  System.out.println("===" + value);
                } else {
                    value = "";//列的值，为空，直接取出去
                }
                jsonObj.put(columnName, value);
            }
            array.put(jsonObj);
        }
        rs.close();
        return array;
    }
    //删除学生信息的方法
    public static void delete01(int studentId) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "delete  from student01 where studentId = ?;";
            pre = conn.prepareStatement(sql);
            pre.setInt(1,studentId);
            pre.executeUpdate();
            JDBCUtils.close(conn,pre,re);//关闭数据库
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //修改学生信息的方法
    public static void update01(Integer age,Integer classId,String className,String createTime,String name,String phone,String remark,String sex,Integer studentId,String studentNo ) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        try {
            conn = JDBCUtils.getConnection();
            //获取当前日期
            java.util.Date date = new Date();
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String dateStr =  format.format(date);
            System.out.println(name);
            System.out.println(studentId);
            String sql = "update student01 set age = ?,classId = ? ,className = ?,createTime = ?,name = ?,phone = ?,remark = ?,sex = ?, studentId = ?, studentNo = ? where studentId = ?;";
            pre = conn.prepareStatement(sql);
            pre.setInt(1, age);
            pre.setInt(2, classId);
            pre.setString(3, className);
            pre.setString(4, dateStr);
            pre.setString(5, name);
            pre.setString(6, phone);
            pre.setString(7, remark);
            pre.setString(8, sex);
            pre.setInt(9, studentId);
            pre.setString(10, studentNo);
            pre.setInt(11,studentId);
            pre.executeUpdate();
            JDBCUtils.close(conn,pre,re);//关闭数据库
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    //查询学生信息的方法
    public static String[] selectSingle(int studentId) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        String data[] = new String[10];
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from student01 where studentId = ?";
            pre = conn.prepareStatement(sql);
            pre.setInt(1,studentId);
            ResultSet rs = pre.executeQuery();

            ArrayList dataArr = new ArrayList();
            while(rs.next()){
                data[0]=rs.getString("age");
                data[1]=rs.getString("classId");
                data[2]=rs.getString("className");
                data[3]=rs.getString("createTime");
                data[4]=rs.getString("name");
                data[5]=rs.getString("phone");
                data[6]=rs.getString("remark");
                data[7]=rs.getString("sex");
                data[8]=rs.getString("studentId");
                data[9]=rs.getString("studentNo");
            }
            JDBCUtils.close(conn,pre,re);//关闭数据库
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return data;
    }
    //拆分获取数据的方法,该方法有巨大缺陷,待解决
    public static String[] split02(String str){
        String[] stri =  str.split("&");
        String[] strArr = new String[10];
        String[] str0 = new String[2];
        str0 = stri[0].split("=");
        strArr[0] = str0[1];
        System.out.println(str0[1]);

        String[] str1 = new String[2];
        str1 = stri[1].split("=");
        strArr[1] = str1[1];
        System.out.println(str1[1]);

        String[] str2 = new String[2];
        str2 = stri[2].split("=");
        strArr[2] = str2[1];
        System.out.println(str2[1]);

        String[] str3 = new String[2];
        str3 = stri[3].split("=");
        strArr[3] = str3[1];
        System.out.println(str3[1]);

        String[] str4 = new String[2];
        str4 = stri[4].split("=");
        strArr[4] = str4[1];
        System.out.println(str4[1]);

        String[] str5 = new String[2];
        str5 = stri[5].split("=");
        strArr[5] = str5[1];
        System.out.println(str5[1]);

        String[] str6 = new String[2];
        str6 = stri[6].split("=");
        strArr[6] = str6[1];
        if(str6[1]==null){
            System.out.println("null");
        }else {
            System.out.println(str6[1]);
        }

        String[] str7 = new String[2];
        str7 = stri[7].split("=");
        strArr[7] = str7[1];
        System.out.println(str7[1]);

        String[] str8 = new String[2];
        str8 = stri[8].split("=");
        strArr[8] = str8[1];
        System.out.println(str8[1]);

        String[] str9 = new String[2];
        str9 = stri[9].split("=");
        strArr[9] = str9[1];
        System.out.println(str9[1]);
        return strArr;
    }
    public static String getTime(){
        //获取当前日期
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateStr =  format.format(date);
        return dateStr;
    }
    //查询全部班级数据
    public static String selectClassAll(String pageNum1,String pageSize1,String classNo,String name) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        JSONArray arr = new JSONArray();
        try {
            conn =  JDBCUtils.getConnection();
            int pageIndex = (Integer.valueOf(pageNum1) - 1) * 10;
            int pageSize = Integer.valueOf(pageSize1);
            //拼接where条件字段
            String whereStr = "where 1=1";
            if (classNo != null && !"".equals(classNo)) {
                whereStr = whereStr + " and classNo like '%" + classNo + "%'";
            }
            if (name != null && !"".equals(name)) {
                whereStr = whereStr + " and name like '%" + name + "%'";
            }
            String sql = "select * from t_class " + whereStr + " limit " + pageIndex + "," + pageSize;
            pre = conn.prepareStatement(sql);
            //ResultSet rs = pre.executeQuery(sql);
            //返回查询的结果
            re = pre.executeQuery();
            arr =resultSetToJson(re);
            JDBCUtils.close(conn,pre,re);//关闭数据库
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return arr.toString();
        //return DBDRIVER;
    }
    public static void addClass(String classNo,String name,String remark,String type ) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        try {
            conn = JDBCUtils.getConnection();
            Statement stmt = null;
            String sql = "insert into t_class (classNo,createTime,name,remark,type) values(?,?,?,?,?)";
            pre = conn.prepareStatement(sql);
            //pre.setInt(1, classId);
            //pre.setInt(2, classId);
            //pre.setString(2, className);
            pre.setString(1, classNo);
            pre.setString(2, MysqlMethod.getTime());
            pre.setString(3, name);
            pre.setString(4, remark);
            pre.setString(5, type);
            //pre.setInt(9, studentId);
            // pre.setString(7, studentNo);
            pre.executeUpdate();
            JDBCUtils.close(conn,pre,re);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    //查询单个班级信息的方法
    public static String[] selectSingleclass(int classId) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        String data[] = new String[6];
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from t_class where classId = ?";
            pre = conn.prepareStatement(sql);
            pre.setInt(1,classId);
            ResultSet rs = pre.executeQuery();
            ArrayList dataArr = new ArrayList();
            while(rs.next()){
                data[0]=rs.getString("classId");
                data[1]=rs.getString("classNo");
                data[2]=rs.getString("createTime");
                data[3]=rs.getString("name");
                data[4]=rs.getString("remark");
                data[5]=rs.getString("type");
            }
            JDBCUtils.close(conn,pre,re);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return data;
    }
    public static String[] split03(String str){
        String[] stri =  str.split("&");
        String[] strArr = new String[6];
        String[] str0 = new String[2];
        str0 = stri[0].split("=");
        strArr[0] = str0[1];
        System.out.println(str0[1]);

        String[] str1 = new String[2];
        str1 = stri[1].split("=");
        strArr[1] = str1[1];
        System.out.println(str1[1]);

        String[] str2 = new String[2];
        str2 = stri[2].split("=");
        strArr[2] = str2[1];
        System.out.println(str2[1]);

        String[] str3 = new String[2];
        str3 = stri[3].split("=");
        strArr[3] = str3[1];
        System.out.println(str3[1]);

        String[] str4 = new String[2];
        str4 = stri[4].split("=");
        strArr[4] = str4[1];
        System.out.println(str4[1]);

        String[] str5 = new String[2];
        str5 = stri[5].split("=");
        strArr[5] = str5[1];
        System.out.println(str5[1]);
        return strArr;
    }
    //修改学生信息的方法
    public static void update02(Integer classId,String classNo,String createTime,String name,String remark,Integer type ) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        try {
            conn = JDBCUtils.getConnection();
            //获取当前日期
            java.util.Date date = new Date();
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String dateStr =  format.format(date);
            String sql = "update t_class set classNo = ?,createTime = ?,name = ?,remark = ?,type = ? where classId = ?;";
            pre = conn.prepareStatement(sql);
            pre.setString(1, classNo);
            pre.setString(2, dateStr);
            pre.setString(3, name);
            pre.setString(4, remark);
            pre.setInt(5,type);
            pre.setInt(6,classId);
            pre.executeUpdate();
            JDBCUtils.close(conn,pre,re);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //删除学生信息的方法
    public static void delete02(int classId) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "delete  from t_class where classId = ?;";
            pre = conn.prepareStatement(sql);
            pre.setInt(1,classId);
            pre.executeUpdate();
            JDBCUtils.close(conn,pre,re);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //创建student对象并赋值
//        List<StudentInfo> studentInfoList = Arrays.asList(
//               new StudentInfo(18,1,"15班",dateStr,"张三","666666","","1",1001,"31001"),
//               new StudentInfo(19,1,"15班",dateStr,"李四","888888","","1",1002,"31002"),
//               new StudentInfo(20,1,"15班",dateStr,"小芳","999999","","2",1003,"31003")
//        );
    //studentListView.setRows(studentInfoList);//把设置好的信息赋给学生对象
    //转为json数组
//        Object objectMapper = new ObjectMapper();
//        String result = ((ObjectMapper) objectMapper).writeValueAsString(studentListView);
}
